-- 

create pluggable database VORAS admin user VORAS_ADM identified by VORAS_ADM;

alter pluggable database VORAS open instances=ALL;
alter pluggable database VORAS save state instances=ALL;


Step 1: Configure the Wallet Root
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~ create directory for storing TDE WALLET 
~~ in a RAC configuration, this directory can be on a shared file system or manually copied from install node to all other nodes

mkdir -p /app/base/admin/HUTTPRD/wallet/tde

CDB$ROOT>  alter system set WALLET_ROOT="/app/base/admin/HUTTPRD/wallet" scope=spfile sid='*';

srvctl stop database -d HUTTPRD
srvctl start database -d HUTTPRD

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both sid='*';

Step 2: Create the password protected key store
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CDB$ROOT>   administer key management create keystore '/app/base/admin/HUTTPRD/wallet/tde' identified by secret;

~~ previous command will create the file ewallet.p12
ls -l /app/base/admin/HUTTPRD/wallet/tde/ewallet.p12



set lines 300 
column WRL_PARAMETER format a40
select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID,INST_ID from gv$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                            STATUS                             CON_ID    INST_ID
-------------------- ---------------------------------------- ------------------------------ ---------- ----------
FILE                 /app/base/admin/HUTTPRD/wallet/tde/      NOT_AVAILABLE                           1          2
FILE                                                          NOT_AVAILABLE                           2          2
FILE                                                          NOT_AVAILABLE                           3          2
FILE                 /app/base/admin/HUTTPRD/wallet/tde/      CLOSED                                  1          1
FILE                                                          CLOSED                                  2          1
FILE                                                          CLOSED                                  3          1

~~ we have status NOT_AVAILABLE for 2nd instance because the wallet has not been yet copied to 2nd

Step 3: Open the key store CDB & PDB
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

administer key management set keystore open force keystore identified by secret container = all;


set lines 300 
column WRL_PARAMETER format a40
select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID,INST_ID from gv$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                            STATUS                             CON_ID    INST_ID
-------------------- ---------------------------------------- ------------------------------ ---------- ----------
FILE                 /app/base/admin/HUTTPRD/wallet/tde/      OPEN_NO_MASTER_KEY                      1          1
FILE                                                          OPEN_NO_MASTER_KEY                      2          1
FILE                                                          OPEN_NO_MASTER_KEY                      3          1
FILE                 /app/base/admin/HUTTPRD/wallet/tde/      NOT_AVAILABLE                           1          2
FILE                                                          NOT_AVAILABLE                           2          2
FILE                                                          NOT_AVAILABLE                           3          2


Step 4.1: Create the master key for the container database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


administer key management set key identified by secret with backup;

set lines 300 
column WRL_PARAMETER format a40 
column NAME format a10

select 
	a.INST_ID, b.NAME, a.STATUS, a.WRL_TYPE, a.WRL_PARAMETER
from 
	gv$encryption_wallet a, gv$pdbs b
where 
  a.con_id = b.con_id (+)
order by
  a.INST_ID, b.NAME, a.STATUS
;

   INST_ID NAME       STATUS                         WRL_TYPE             WRL_PARAMETER
---------- ---------- ------------------------------ -------------------- ----------------------------------------
         1 PDB$SEED   OPEN                           FILE
         1 PDB$SEED   OPEN                           FILE
         1 VORAS      OPEN_NO_MASTER_KEY             FILE
         1 VORAS      OPEN_NO_MASTER_KEY             FILE
         1            OPEN                           FILE                 /app/base/admin/HUTTPRD/wallet/tde/
         2 PDB$SEED   NOT_AVAILABLE                  FILE
         2 PDB$SEED   NOT_AVAILABLE                  FILE
         2 VORAS      NOT_AVAILABLE                  FILE
         2 VORAS      NOT_AVAILABLE                  FILE
         2            NOT_AVAILABLE                  FILE                 /app/base/admin/HUTTPRD/wallet/tde/


Step 4.2: Create the master key for PDB (unified mode)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

alter session set container=VORAS;
administer key management set key identified by secret with backup;

set lines 300 
column WRL_PARAMETER format a40 
column NAME format a10

select 
	a.INST_ID, b.NAME, a.STATUS, a.WRL_TYPE, a.WRL_PARAMETER
from 
	gv$encryption_wallet a, gv$pdbs b
where 
  a.con_id = b.con_id (+)
order by
  a.INST_ID, b.NAME, a.STATUS
;

   INST_ID NAME       STATUS                         WRL_TYPE             WRL_PARAMETER
---------- ---------- ------------------------------ -------------------- ----------------------------------------
         1 PDB$SEED   OPEN                           FILE
         1 PDB$SEED   OPEN                           FILE
         1 VORAS      OPEN                           FILE
         1 VORAS      OPEN                           FILE
         1            OPEN                           FILE                 /app/base/admin/HUTTPRD/wallet/tde/
         2 PDB$SEED   NOT_AVAILABLE                  FILE
         2 PDB$SEED   NOT_AVAILABLE                  FILE
         2 VORAS      NOT_AVAILABLE                  FILE
         2 VORAS      NOT_AVAILABLE                  FILE
         2            NOT_AVAILABLE                  FILE                 /app/base/admin/HUTTPRD/wallet/tde/



Step 5: Create an autologin keystorefor the CDB
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

administer key management create auto_login keystore from keystore '/app/base/admin/HUTTPRD/wallet/tde' identified by secret; 

ls -ltr /app/base/admin/HUTTPRD/wallet/tde
total 24
-rw-------. 1 oracle asmadmin 2555 Apr 12 17:52 ewallet_2020041215523610.p12
-rw-------. 1 oracle asmadmin 3995 Apr 12 22:26 ewallet_2020041220264943.p12
-rw-------. 1 oracle asmadmin 5467 Apr 12 22:26 ewallet.p12
-rw-------. 1 oracle asmadmin 5512 Apr 12 22:35 cwallet.sso

-- cwallet.sso has been created

-- copy security files to ALL RAC nodes
cd /app/base/admin/HUTTPRD/wallet/tde
scp -rp * vortex-db02:/app/base/admin/HUTTPRD/wallet/tde


set lines 300 
column WRL_PARAMETER format a40 
column NAME format a10

select 
	a.INST_ID, b.NAME, a.STATUS, a.WRL_TYPE, a.WRL_PARAMETER
from 
	gv$encryption_wallet a, gv$pdbs b
where 
  a.con_id = b.con_id (+)
order by
  a.INST_ID, b.NAME, a.STATUS
;

   INST_ID NAME       STATUS                         WRL_TYPE             WRL_PARAMETER
---------- ---------- ------------------------------ -------------------- ----------------------------------------
         1 PDB$SEED   OPEN                           FILE
         1 PDB$SEED   OPEN                           FILE
         1 VORAS      OPEN                           FILE
         1 VORAS      OPEN                           FILE
         1            OPEN                           FILE                 /app/base/admin/HUTTPRD/wallet/tde/
         2 PDB$SEED   OPEN                           FILE
         2 PDB$SEED   OPEN                           FILE
         2 VORAS      OPEN                           FILE
         2 VORAS      OPEN                           FILE
         2            OPEN                           FILE                 /app/base/admin/H



Step 6.1: Encrypt tablespaces online (CDB)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CDB$ROOT> select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;


TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    NO
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
TEMP                           ONLINE    NO
UNDOTBS2                       ONLINE    NO
USERS                          ONLINE    NO


CDB$ROOT> alter tablespace USERS encryption online encrypt;
CDB$ROOT> alter tablespace SYSTEM  encryption online encrypt;


CDB$ROOT> select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;

TABLESPACE_NAME                STATUS    ENC
------------------------------ --------- ---
SYSTEM                         ONLINE    YES
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
TEMP                           ONLINE    NO
UNDOTBS2                       ONLINE    NO
USERS                          ONLINE    YES




RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;


Manually OPEN/CLOSE keystore
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SYSKM@HUTTPRD1:CDB$ROOT> administer key management set keystore open force keystore identified by secret container = all;

keystore altered.

SYSKM@HUTTPRD1:CDB$ROOT> administer key management set keystore close identified by secret;

keystore altered.

